package com.iceframework.core.utils;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * excel读取
 */
public class ExcelReader {

    public final static String EXCEL2003_SUFFIX = ".xls";
    public final static String EXCEL2010_SUFFIX = ".xlsx";

    private int sheetIndex=0;
    private int titleRow=0;
    private int startCol=0;
    private int endCol=-1;
    private int startRow=0;
    private int endRow=-1;
    private String excelVersion=EXCEL2010_SUFFIX;
    private int colNum;
    private int rowNum;

    private Sheet sheet;
    private String[] names;
    private ExcelCellType[] types;
    private ExcelNullType[] nullable;

    List<List<String>> tableData;

    public ExcelReader(){

    }

    /**
     *
     * @param sheetIndex 第几个sheet
     * @param startCol 从第几列开始读
     */
    public ExcelReader(int sheetIndex,int startCol){
        this.sheetIndex = sheetIndex - 1;
        this.startCol = startCol - 1;
    }

    /**
     *
     * @param sheetIndex 第几个sheet
     * @param startCol 从第几列开始读
     * @param titleRow 标题行（读取excel会从标题行的下一行开始读取）
     */
    public ExcelReader(int sheetIndex,int startCol,int titleRow){
        this.sheetIndex = sheetIndex - 1;
        this.startCol = startCol - 1;
        this.titleRow = titleRow - 1;
        startRow = titleRow;
    }

    public List<List<String>> readExcel(InputStream source){
        tableData=new ArrayList<List<String>>();
        initProperties(source);
        initTitle();
        initData();
        return tableData;
    }

    private void initData() {
        // 除标题行数据行数
        int dataRow = rowNum - 1;
        for(int i=0;i<dataRow;i++){
            List<String> rowList=new ArrayList<>();
            Row row=sheet.getRow(i+startRow+1);
            for (int j = 0; j < colNum; j++) {
                Cell cell=row.getCell(j + startCol);
                String value=null;
                if(cell!=null) {
                    if (CellType.NUMERIC == cell.getCellType()) {
                        //判断是否为日期类型
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            //用于转化为日期格式
                            Date d = cell.getDateCellValue();
                            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            value = formater.format(d);
                        } else {
                            // 用于格式化数字，只保留数字的整数部分
                            DecimalFormat df = new DecimalFormat("########");
                            value = df.format(cell.getNumericCellValue());
                        }
                    } else {
                        value = cell.getStringCellValue();
                    }
                }
                Object ob=str2Obj(value, types[j]);
                rowList.add(String.valueOf(ob));
            }
            tableData.add(rowList);
        }
    }

    private void initTitle() {
        names = new String[colNum];
        types = new ExcelCellType[colNum];
        nullable = new ExcelNullType[colNum];

        Row row = sheet.getRow(titleRow);
        DateFormat dfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        DecimalFormat ndf = new DecimalFormat("########");

        for (int i = 0; i < colNum; i++) {
            String typestr=null;
            Cell cell=row.getCell(i + startCol);
            if(cell!=null) {
                if (CellType.NUMERIC == cell.getCellType()) {
                    //判断是否为日期类型
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        //用于转化为日期格式
                        Date d = cell.getDateCellValue();
                        typestr = dfm.format(d);
                    } else {
                        // 用于格式化数字，只保留数字的整数部分
                        typestr = ndf.format(cell.getNumericCellValue());
                    }
                } else {
                    typestr = cell.getStringCellValue();
                }
            }
            String[] values = StringUtils.split(typestr,"/");
            if(values.length>0){
                names[i] = values[0];
            }else{
                names[i] = "COL" + (i + startCol);;
            }
            if(values.length>1){
                types[i] = ExcelCellType.toEnum(values[1]);
            }else{
                types[i] = ExcelCellType.String;
            }
            if (values.length > 2) {
                nullable[i] = ExcelNullType.toEnum(values[2]);
            } else {
                nullable[i] = ExcelNullType.nullable;
            }
        }
    }

    private void initProperties(InputStream source) {
        Workbook workbook = null;
        try {
            if (excelVersion.equals(EXCEL2003_SUFFIX)) {
                workbook = new HSSFWorkbook(source);
            } else {
                workbook = new XSSFWorkbook(source);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = workbook.getSheetAt(sheetIndex);
        if (endRow < 0) {
            endRow = sheet.getPhysicalNumberOfRows();
        }
        if (endCol < 0) {
            endCol = sheet.getRow(titleRow).getPhysicalNumberOfCells();
        }

        colNum = endCol;
        rowNum = endRow;

    }

    public enum ExcelCellType {
        String, Interger, Double,Boolean, Date, DateTime;

        public static ExcelCellType toEnum(String str) {
            if ("String".equalsIgnoreCase(str))
                return ExcelCellType.String;
            if ("Interger".equalsIgnoreCase(str))
                return ExcelCellType.Interger;
            if ("Double".equalsIgnoreCase(str))
                return ExcelCellType.Double;
            if ("Boolean".equalsIgnoreCase(str))
                return ExcelCellType.Boolean;
            if ("Date".equalsIgnoreCase(str))
                return ExcelCellType.Date;
            if ("DateTime".equalsIgnoreCase(str))
                return ExcelCellType.DateTime;
            throw new RuntimeException("type: " + str + " is not support");
        }
    }

    public enum ExcelNullType {
        notnull, unique, nullable;

        public static ExcelNullType toEnum(String str) {
            if ("notnull".equalsIgnoreCase(str))
                return ExcelNullType.notnull;
            if ("unique".equalsIgnoreCase(str))
                return ExcelNullType.unique;
            if ("nullable".equalsIgnoreCase(str))
                return ExcelNullType.nullable;
            throw new RuntimeException("nulltype: " + str + " is not support");
        }
    }

    private Object str2Obj(String str,ExcelCellType type){
        SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");
        Object re=null;
        if(StringUtils.isEmpty(str)){
            return re;
        }
        switch (type) {
            case String:
                re=str;
                break;
            case Interger:
                re=Integer.parseInt(str);
                break;
            case Double:
                re=Double.parseDouble(str);
                break;
            case Date:
                try {
                    re=sdf.parse(str);
                } catch (ParseException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                break;
            case DateTime:
                break;
            case Boolean:
                re=Boolean.parseBoolean(str);
                break;
            default:
                break;
        }
        return re;
    }

}